/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nc.kyiv.yellow.sociallibrary.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author My
 */
public class MyDAO {
    DataSource source = null;

    public MyDAO() throws Exception {
        if (source != null) {
            return;
        }
        try {
            Context ic = new InitialContext();
            source = (DataSource) ic.lookup("jdbc/sociallibraryConnection");
        } catch (NamingException ex) {
            throw ex;
        }
        if (source == null) {
            throw new Exception("Lookup failed");
        }
    }
    
    public Connection getConnection() throws SQLException {
        try {
            Connection conn = source.getConnection();
            conn.setAutoCommit(false);
            return conn;
        } catch (SQLException ex) {
            throw ex;
        }
    }
    
    public void releaseConnection(Connection conn) throws SQLException {
        try {
            conn.close();
        } catch (SQLException ex) {
            throw ex;
        }
    }
    
public LibUser getUserByLogin(Map params) throws SQLException, Exception {
        Connection conn = null;
        LibUser user = null;
        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            /*ResultSet rs = stmt.executeQuery("SELECT login, first_name, pass FROM lib_user WHERE login = '" 
             + params.get("login") + "' AND pass = '" + params.get("pass") + "'");*/

            ResultSet rs = stmt.executeQuery("SELECT id, role, login, first_name, pass FROM lib_user WHERE login = '"
                    + params.get("login") + "' AND pass = '" + params.get("pass") + "'");

            if (rs.next()) {
                user = new LibUser(rs.getString("login"), rs.getString("first_name"), rs.getString("pass"));
                user.setRole((int) rs.getDouble("role"));
                user.setId((int) rs.getDouble("id"));
            } else {
                user = null;
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
        return user;
    }
    
    public void registerUser(Map params) throws SQLException {
        Connection conn = null;
        String query = "INSERT INTO lib_user (role, login, last_name, first_name, pass, email, status_id, reg_date) "
                + "VALUES("
                + "(SELECT id FROM role WHERE role = 'beginner'),"
                + "'" + params.get("login") + "',"
                + "'" + params.get("last_name") + "',"
                + "'" + params.get("first_name") + "',"
                + "'" + params.get("pass") + "',"
                + "'" + params.get("email") + "',"
                + "(SELECT id FROM user_status WHERE status = 'active'),"
                + "TO_DATE(sysdate, 'DD-MM-YYYY'))";

        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.execute(query);

        } catch (SQLException ex) {
            throw ex;
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
    }

    public void addBook(Map params) throws SQLException {
        Connection conn = null;
        conn = getConnection();
        
        String title = (String)params.get("title");
        title = title.replace("'", "''");
        String description = (String)params.get("description");
        description = description.replace("'", "''");
        
        String query = "INSERT INTO book (isbn, title, description, page_number, regist_date, moder_status, image) "
                + "values("
                + "'" + params.get("isbn") + "',"
                + "'" + title + "',"
                + "'" + description + "',"
                + params.get("numberOfPages") + ","
                + "TO_DATE(sysdate, 'DD-MM-YYYY'),"
                + "(select id from moder_status where status = 'new'),"
                + "'" + params.get("image") + "')";
    
        try {
            Statement stmt = conn.createStatement();
            stmt.execute(query);

        } catch (SQLException ex) {
            throw ex;
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
    }

    public void registerUserWithSocNetwork(Map params) throws SQLException {
        Connection conn = null;
       
        String checkQuery = "SELECT login FROM lib_user WHERE login = " + "'" + params.get("login") + "'";
        
        String insertQuery = "INSERT INTO lib_user (role, login, last_name, first_name, pass, email, status_id, reg_date) "
                + "VALUES("
                + "(SELECT id FROM role WHERE role = 'beginner'),"
                + "'" + params.get("login") + "',"
                + "'" + params.get("last_name") + "',"
                + "'" + params.get("first_name") + "',"
                + "'" + params.get("pass") + "',"
                + "'" + params.get("email") + "',"
                + "(SELECT id FROM user_status WHERE status = 'active'),"
                + "TO_DATE(sysdate, 'DD-MM-YYYY'))";
        
        String updateQuery = "UPDATE lib_user "
                + "SET last_name = " + "'" + params.get("last_name") + "',"
                + "first_name = " + "'" + params.get("first_name") + "',"
                + "email = " + "'" + params.get("email") + "'"
                + "WHERE login = " + "'" + params.get("login") + "'";

        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(checkQuery);
            
            if (rs.isBeforeFirst()) {
                stmt.executeUpdate(updateQuery);
            } else {
                stmt.execute(insertQuery);
            }

        } catch (SQLException ex) {
            throw ex;
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
    }
}
